#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
create database if not exists edu_rpt;
use edu_rpt;
--每日用户访问量
drop table if exists kxh_rpt_day_sid_cnt;
create table if not exists kxh_rpt_day_sid_cnt(
    create_time_day string comment'天',
   sid_count                bigint comment '访问用户量'
 )   comment '每日访问情况'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');
-- 每日地区用户访问量
drop table if exists kxh_rpt_month_area_sid_cnt;
create table if not exists kxh_rpt_month_area_sid_cnt(
    create_time_day string comment'天',
     area                         string  comment '地域',
   sid_count               bigint comment '访问用户量'
 )   comment '每日地区用户访问量'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');

-- 7月份中国各省的咨询情况
drop table if exists kxh_rpt_month_shengfen_con_rate_cnt;
create table if not exists kxh_rpt_month_shengfen_con_rate_cnt(
    create_time_day string comment'天',
     shengfen                 string  comment '省份',
     session_id_count         bigint   comment '访问Session个数',
     sid_count                bigint comment '访问用户量',
     con_rate             decimal(38,4)  comment'咨询率'
 )   comment '7月份中国各省的咨询情况'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');


--7月的来源渠道的访问用户数量排行
drop table if exists kxh_rpt_origin_channel_sid_cnt_top;
create table if not exists kxh_rpt_origin_channel_sid_cnt_top(
       year_month string comment'月',
       origin_channel   string   comment '投放渠道',
        sid_count     bigint comment '访问用户量'
)comment '7月份通过来源渠道访问用户量排行榜'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');"